package com.hsnn.medstgmini.pro.project.dao.impl;

import java.math.BigInteger;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.ProdProfileDao;
import com.hsnn.medstgmini.pro.project.model.BidProjectDetails;
import com.hsnn.medstgmini.pro.project.model.form.ProdProfileForm;
import com.hsnn.medstgmini.pro.project.model.form.ProdProjectForm;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.MapToBeanUtil;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 生产企业申报资质
 * @author 蔡春龙
 * @date 2015年8月26日
 */
@Repository
public class ProdProfileDaoImpl implements ProdProfileDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;

	@Autowired
	private HibernateSqlUtil sqlUtil;

	/**
	 * @category 查询某企业的企业资质
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	public Pagination getProfileList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.comp_id compId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT( a.profile_starttime, '%Y-%m-%d' ) profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.status, a.remarks FROM std_compprofile a WHERE a.comp_id = :compId AND NOT EXISTS ( SELECT * FROM bid_projcompprofile b WHERE a.compprofile_id = b.compprofile_id AND b.proj_id = :projId ) ");

		ProdProfileForm form = (ProdProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList2(sb.toString(), params, page);

		return page;
	}

	/**
	 * @category 查询某企业资质审核结果列表
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	public Pagination getProfileAuditResultList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.comp_id compId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT(a.profile_starttime, '%Y-%m-%d') profileStarttime, DATE_FORMAT(a.profile_endtime, '%Y-%m-%d') profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.status, a.remarks, b.proj_compprofile_id projCompprofileId, b.proj_id projId, b.audit_status auditStatus, DATE_FORMAT(b.audit_datetime, '%Y-%m-%d') auditDatetime, b.audit_remark auditRemark, b.complain_status complainStatus, DATE_FORMAT(b.complain_datetime, '%Y-%m-%d') complainDatetime, b.complain_remark complainRemark, DATE_FORMAT(b.complain_audit_datetime, '%Y-%m-%d') complainAuditDatetime, b.complain_audit_remark complainAuditRemark FROM std_compprofile a JOIN bid_projcompprofile b ON a.compprofile_id = b.compprofile_id AND b.proj_id = :projId AND b.status = 1 WHERE a.comp_id = :compId ");

		ProdProfileForm form = (ProdProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());

		Integer auditStatus = form.getAuditStatus();
		if (auditStatus != null) {
			sb.append(" AND b.audit_status = :auditStatus ");
			params.put("auditStatus", auditStatus);
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList2(sb.toString(), params, page);

		return page;
	}
	
	/**
	 * @category 查询某企业资质申报列表
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getProfileApplyResultList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.comp_id compId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT(a.profile_starttime, '%Y-%m-%d') profileStarttime, DATE_FORMAT(a.profile_endtime, '%Y-%m-%d') profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a.status, a.remarks, b.proj_compprofile_id projCompprofileId, b.proj_id projId, b.audit_status auditStatus, DATE_FORMAT(b.audit_datetime, '%Y-%m-%d') auditDatetime, b.audit_remark auditRemark, b.complain_status complainStatus, DATE_FORMAT(b.complain_datetime, '%Y-%m-%d') complainDatetime, b.complain_remark complainRemark, DATE_FORMAT(b.complain_audit_datetime, '%Y-%m-%d') complainAuditDatetime, b.complain_audit_remark complainAuditRemark FROM std_compprofile a JOIN bid_projcompprofile b ON a.compprofile_id = b.compprofile_id AND b.proj_id = :projId AND b.status = 1 WHERE a.comp_id = :compId and b.audit_status=0 ");

		ProdProfileForm form = (ProdProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());

		Integer auditStatus = form.getAuditStatus();
		if (auditStatus != null) {
			sb.append(" AND b.audit_status = :auditStatus ");
			params.put("auditStatus", auditStatus);
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");
		sqlUtil.getSqlPageMapList2(sb.toString(), params, page);
		return page;
	}

	/**
	 * @category 查询企业资质澄清列表
	 * @date 2015年9月2日
	 * @param page
	 * @return
	 */
	public Pagination getProfileComplainList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.comp_id compId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT( a.profile_starttime, '%Y-%m-%d' ) profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a. STATUS, a.remarks, b.proj_compprofile_id projCompprofileId, b.proj_id projId, b.audit_status auditStatus, DATE_FORMAT( b.audit_datetime, '%Y-%m-%d' ) auditDatetime, b.audit_remark auditRemark, b.complain_status complainStatus, DATE_FORMAT( b.complain_datetime, '%Y-%m-%d' ) complainDatetime, b.complain_remark complainRemark, DATE_FORMAT( b.complain_audit_datetime, '%Y-%m-%d' ) complainAuditDatetime, b.complain_audit_remark complainAuditRemark FROM std_compprofile a JOIN bid_projcompprofile b ON a.compprofile_id = b.compprofile_id AND b.status = 1 WHERE a.comp_id = :compId AND b.proj_id = :projId AND b.audit_status = 2 AND b.complain_status = 3 ");

		ProdProfileForm form = (ProdProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList2(sb.toString(), params, page);

		return page;
	}

	/**
	 * @category 提交企业资质
	 * @date 2015年8月27日
	 * @param list
	 * @param user
	 */
	public void submitProfileList(List<ProdProfileForm> list, SysUser user) {
		Session session = factory.getCurrentSession();
		if (list != null && !list.isEmpty()) {
			List<String> ids = new LinkedList<String>();
			for (ProdProfileForm form : list) {
				String sql = " SELECT count(*) FROM bid_projcompprofile WHERE proj_id = :projId AND compprofile_id = :compprofileId ";
				SQLQuery query = session.createSQLQuery(sql);
				query.setString("compprofileId", form.getCompprofileId());
				query.setString("projId", form.getProjId());
				Long records = ((BigInteger) query.uniqueResult()).longValue();

				if (records == 0) {
					String id = UUID.randomUUID().toString();
					ids.add(id);
					sql = " INSERT INTO bid_projcompprofile(proj_compprofile_id, proj_id, compprofile_id, audit_status, status, complain_status, upd_datetime, upd_user) VALUES (:id, :projId, :compprofileId, 0, 1, 3, sysdate(), :updUser) ";
					query = session.createSQLQuery(sql);
					query.setString("id", id);
					query.setString("compprofileId", form.getCompprofileId());
					query.setString("projId", form.getProjId());
					query.setString("updUser", user.getId());
					query.executeUpdate();
				}
			}
			session.flush();

			saveProjectProfileLog(ids);
		}
	}

	/**
	 * @category 保存项目资质关系表日志
	 * @date 2015年8月27日
	 * @param ids
	 */
	private void saveProjectProfileLog(List<String> ids) {
		if (ids != null && !ids.isEmpty()) {
			Session session = factory.getCurrentSession();
			String sql = " INSERT INTO bid_projcompprofile_log SELECT UUID(), a.* FROM bid_projcompprofile a WHERE proj_compprofile_id IN (:ids) ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameterList("ids", ids);
			query.executeUpdate();
			session.flush();
		}
	}

	/**
	 * @category 澄清企业资质
	 * @date 2015年8月31日
	 * @param page
	 * @param stdCompprofile
	 */
	public void complainProfile(Pagination page) {
		ProdProfileForm form = (ProdProfileForm) page.getForm();
		if (form != null) {
			String projCompprofileId = form.getProjCompprofileId();
			if (StringUtils.isNotBlank(projCompprofileId)) {
				Session session = factory.getCurrentSession();
				String sql = " update bid_projcompprofile set complain_status = 0, complain_datetime = sysdate(), complain_remark = :complainRemark, upd_datetime = sysdate(), upd_user = :updUser where proj_compprofile_id = :projCompprofileId ";
				SQLQuery query = session.createSQLQuery(sql);
				query.setString("complainRemark", form.getComplainRemark());
				query.setString("projCompprofileId", projCompprofileId);
				query.setString("updUser", form.getUpdUser());
				query.executeUpdate();
				session.flush();

				// 记录日志
				List<String> ids = new LinkedList<String>();
				ids.add(projCompprofileId);
				saveProjectProfileLog(ids);
			}
		}
	}

	/**
	 * @category 企业资质澄清结果查询列表
	 * @date 2015年9月2日
	 * @param page
	 * @return
	 */
	public Pagination getProfileComplainResultList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.compprofile_id compprofileId, a.comp_id compId, a.profile_code profileCode, a.profile_name profileName, a.profile_type profileType, DATE_FORMAT( a.profile_starttime, '%Y-%m-%d' ) profileStarttime, DATE_FORMAT( a.profile_endtime, '%Y-%m-%d' ) profileEndtime, a.profile_pic_addr profilePicAddr, a.profile_pic_shortcut_addr profilePicShortcutAddr, a. STATUS, a.remarks, b.proj_compprofile_id projCompprofileId, b.proj_id projId, b.audit_status auditStatus, DATE_FORMAT( b.audit_datetime, '%Y-%m-%d' ) auditDatetime, b.audit_remark auditRemark, b.complain_status complainStatus, DATE_FORMAT( b.complain_datetime, '%Y-%m-%d' ) complainDatetime, b.complain_remark complainRemark, DATE_FORMAT( b.complain_audit_datetime, '%Y-%m-%d' ) complainAuditDatetime, b.complain_audit_remark complainAuditRemark FROM std_compprofile a JOIN bid_projcompprofile b ON a.compprofile_id = b.compprofile_id AND b.status = 1 WHERE a.comp_id = :compId AND b.proj_id = :projId AND b.audit_status = 2 AND b.complain_status <> 3 ");

		ProdProfileForm form = (ProdProfileForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", form.getCompId());
		params.put("projId", form.getProjId());

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList2(sb.toString(), params, page);

		return page;
	}

	/**
	 * @category 查询已报名项目企业资质申报列表（生产企业）
	 * @date 2015年8月27日 修改日期 2015-9-24 韩守松
	 * @param page
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public Pagination getProdProfileListList(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.proj_id AS projId, MAX(a.name) name, DATE_FORMAT( MAX(a.starttime), '%Y-%m-%d' ) AS starttime, DATE_FORMAT(MAX(a.endtime), '%Y-%m-%d') AS endtime, COUNT(*) totalCount, count(d.compprofile_id) declaredCount, DATE_FORMAT(MAX(h.pro_submit_starttime), '%Y-%m-%d') proSubmitStarttime, DATE_FORMAT(MAX(h.pro_submit_endtime), '%Y-%m-%d') proSubmitEndtime, DATE_FORMAT(MAX(h.pro_audit_pub_starttime), '%Y-%m-%d') proAuditPubStarttime, DATE_FORMAT(MAX(h.pro_audit_pub_endtime), '%Y-%m-%d') proAuditPubEndtime, DATE_FORMAT(MAX(h.complain_starttime), '%Y-%m-%d') complainStarttime, DATE_FORMAT(MAX(h.complain_endtime), '%Y-%m-%d') complainEndtime, DATE_FORMAT(MAX(h.complain_pub_starttime), '%Y-%m-%d') complainPubStarttime, DATE_FORMAT(MAX(h.complain_pub_endtime), '%Y-%m-%d') complainPubEndtime FROM view_bidproject a JOIN bid_projprodcompany b ON a.proj_id = b.proj_id JOIN std_compprofile c ON b.prod_comp_id = c.comp_id AND c.status = 1 AND c.audit_status=1 LEFT JOIN bid_projcompprofile d ON a.proj_id = d.proj_id AND c.compprofile_id = d.compprofile_id left join bid_project_details h ON a.proj_id = h.proj_id WHERE 1 = 1 AND a.proj_cat = 1 AND a.status = 1 AND h.proj_starttime <= SYSDATE() AND h.proj_endtime >= SYSDATE() AND h.is_cur_status_close=1 AND h.cur_status=1 AND b.prod_comp_id = :compId ");

		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);

		if (form != null) {
			String projId = form.getProjId();
			if (StringUtils.isNotBlank(projId)) {
				sb.append(" AND a.proj_id = :projId ");
				params.put("projId", projId);
			}
		}
		sb.append(" GROUP BY a.proj_id order by a.create_datetime desc ");
		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();

		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,
				ProdProjectForm.class);

		page.setRows(projList);

		return page;
	}
	/**
	 * @category 查询已报名项目企业资质申报列表（配送企业）
	 * @date 2015年8月27日 修改日期 2015-9-24 韩守松
	 * @param page
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public Pagination getProdDelfileListList(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();
		if (compInfo == null) {
			return page;
		}
		String compId = compInfo.getCompId();
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.proj_id AS projId,MAX(a.name) name,COUNT(*) totalCount, count(d.compprofile_id) declaredCount, DATE_FORMAT(MAX(h.pro_submit_starttime), '%Y-%m-%d') proSubmitStarttime, DATE_FORMAT(MAX(h.pro_submit_endtime), '%Y-%m-%d') proSubmitEndtime FROM view_bidproject a JOIN bid_projcompany b ON a.proj_id = b.proj_id JOIN std_compprofile c ON b.comp_id = c.comp_id AND c.status = 1 LEFT JOIN bid_projcompprofile d ON a.proj_id = d.proj_id AND c.compprofile_id = d.compprofile_id left join bid_project_details h ON a.proj_id = h.proj_id WHERE a.proj_cat = 1 AND a.status = 1 AND h.proj_starttime <= SYSDATE() AND h.proj_endtime >= SYSDATE() and h.is_cur_status_close=1 and h.cur_status=1   AND b.comp_id = :compId ");
		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);
		if (form != null) {
			String projId = form.getProjId();
			if (StringUtils.isNotBlank(projId)) {
				sb.append(" AND a.proj_id = :projId ");
				params.put("projId", projId);
			}
		}
		sb.append(" GROUP BY a.proj_id order by a.create_datetime desc ");
		List<Map<String, Object>> list = (List<Map<String, Object>>)sqlUtil.getSqlMapList(sb.toString(), params);
		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,ProdProjectForm.class);
		page.setRows(projList);
		return page;
	}
	
	/**
	 * @category 查询企业资质申报结果列表（企业资质查看）
	 * @date 2015年8月27日 修改日期 2015-09-24 韩守松
	 * @param page
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public Pagination getProfileAuditResultListList(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();
		if (compInfo == null) {
			return page;
		}
		String compId = compInfo.getCompId();
		StringBuffer sb = new StringBuffer();
		sb.append(" select ")
		.append("  bpp.proj_id  AS projId, ")
		.append(" MAX(bp.name) name, ")
		.append(" count(bpp.compprofile_id) totalCount,   ")
		.append(" SUM( CASE  WHEN bpp.audit_status = 1  THEN 1 ELSE 0 END ) approveCount, ")
		.append(" SUM( CASE  WHEN bpp.audit_status = 2  THEN 1  ELSE 0 END ) unapproveCount, ")
		.append(" DATE_FORMAT(MAX(bpd.pro_audit_pub_starttime),'%Y-%m-%d %T') proAuditPubStarttime, ")
		.append(" DATE_FORMAT(MAX(bpd.pro_audit_pub_endtime), '%Y-%m-%d %T') proAuditPubEndtime ")
		.append(" from std_compprofile sc ")
		.append(" inner join bid_projcompprofile bpp on sc.compprofile_id = bpp.compprofile_id ")
		.append(" left join bid_project_details bpd on bpp.proj_id = bpd.proj_id ")
		.append(" left join bid_project bp on  bpp.proj_id = bp.proj_id ")
		.append("  where sc.comp_id = :compId ")
		.append("  and sc.status = 1 and sc.audit_status = 1  and bp.proj_cat = 1 AND bpd.is_cur_status_close=1 AND bpd.cur_status =3 ")
		.append(" AND bpd.proj_starttime <= SYSDATE() AND bpd.proj_endtime >= SYSDATE() ");
		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);

		if (form != null) {
			String projId = form.getProjId();
			if (StringUtils.isNotBlank(projId)) {
				sb.append(" AND bpp.proj_Id = :projId ");
				params.put("projId", projId);
			}
		}
		sb.append(" GROUP BY bpp.proj_id order by bp.create_datetime desc ");
		List<Map<String, Object>> list = (List<Map<String, Object>>)sqlUtil.getSqlMapList(sb.toString(), params);
		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,ProdProjectForm.class);
		page.setRows(projList);

		return page;
	}
	
	/**
	 * @category 查询企业资质未通过审核列表
	 * @date 2015年8月27日 修改日期 2015-9-24 韩守松
	 * @param page
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public Pagination getProfileComplainListList(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();
		if (compInfo == null) {
			return page;
		}
		String compId = compInfo.getCompId();
		StringBuffer sb = new StringBuffer();
		sb.append(" select ")
		.append("  bpp.proj_id  AS projId, ")
		.append(" MAX(bp.name) name, ")
		.append(" DATE_FORMAT( MAX(bp.starttime),'%Y-%m-%d' ) AS starttime, ")
		.append(" DATE_FORMAT(MAX(bp.endtime),'%Y-%m-%d') AS endtime, ")
		.append(" count(bpp.compprofile_id) totalCount,   ")
		.append(" SUM( CASE  WHEN bpp.complain_status = 0 THEN 1 ELSE 0 END  ) settleCount, ")
		.append(" SUM( CASE  WHEN bpp.complain_status = 3 THEN 1  ELSE 0 END ) unsettleCount, ")
		.append(" DATE_FORMAT(MAX(bpd.complain_starttime),'%Y-%m-%d %T') complainStarttime, ")
		.append(" DATE_FORMAT(MAX(bpd.complain_endtime),'%Y-%m-%d %T') complainEndtime")
		.append(" from std_compprofile sc ")
		.append(" inner join bid_projcompprofile bpp on sc.compprofile_id = bpp.compprofile_id ")
		.append(" left join bid_project_details bpd on bpp.proj_id = bpd.proj_id ")
		.append(" left join bid_project bp on  bpp.proj_id = bp.proj_id ")
		.append("  where sc.comp_id = :compId ")
		.append("  and sc.status = 1 and sc.audit_status = 1  and bp.proj_cat = 1 and bpp.audit_status = 2 and bpd.cur_status=1 and bpd.is_cur_status_close=4 ")
		.append(" AND bpd.proj_starttime <= SYSDATE() AND bpd.proj_endtime >= SYSDATE() ");
		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);
		if (form != null) {
			String projId = form.getProjId();
			if(StringUtils.isNotBlank(projId)){
				sb.append(" AND bpp.proj_id = :projId ");
				params.put("projId", projId);
			}
		}
		sb.append(" GROUP BY bpp.proj_id order by bp.create_datetime desc ");
		List<Map<String, Object>> list = (List<Map<String, Object>>)sqlUtil.getSqlMapList(sb.toString(), params);
		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,ProdProjectForm.class);
		page.setRows(projList);
		return page;
	}
	
	/**
	 * @category 查询企业资质澄清结果列表
	 * @date 2015年8月27日 修改日期 2015-9-24 韩守松
	 * @param page
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public Pagination getProfileComplainResultListList(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();
		if (compInfo == null) {
			return page;
		}
		String compId = compInfo.getCompId();
		StringBuffer sb = new StringBuffer();
		sb.append(" select ")
		.append("  bpp.proj_id  AS projId, ")
		.append(" MAX(bp.name) name, ")
		.append(" SUM( CASE  WHEN bpp.complain_status = 1 THEN 1 ELSE 0 END  ) settleApproveCount, ")
		.append(" SUM( CASE  WHEN bpp.complain_status = 2 THEN 1  ELSE 0 END ) settleUnsettleCount, ")
		.append(" DATE_FORMAT(MAX(bpd.complain_pub_starttime),'%Y-%m-%d') complainPubStarttime,")
		.append(" DATE_FORMAT(MAX(bpd.complain_pub_endtime),'%Y-%m-%d') complainPubEndtime  ")
		.append(" from std_compprofile sc ")
		.append(" inner join bid_projcompprofile bpp on sc.compprofile_id = bpp.compprofile_id ")
		.append(" left join bid_project_details bpd on bpp.proj_id = bpd.proj_id ")
		.append(" left join bid_project bp on  bpp.proj_id = bp.proj_id ")
		.append("  where sc.comp_id = :compId ")
		.append("  and sc.status = 1 and sc.audit_status = 1  and bp.proj_cat = 1 and bpp.audit_status = 2  and bpp.complain_status !=3 ")
		.append(" AND bpd.proj_starttime <= SYSDATE() AND bpd.proj_endtime >= SYSDATE() and bpd.is_cur_status_close=1 and bpd.cur_status=5 ");
		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);
		if (form != null) {
			String projId = form.getProjId();
			if(StringUtils.isNotBlank(projId)){
				sb.append(" AND bpp.proj_id = :projId ");
				params.put("projId", projId);
			}
		}
		sb.append(" GROUP BY bpp.proj_id order by bp.create_datetime desc ");
		List<Map<String, Object>> list = (List<Map<String, Object>>)sqlUtil.getSqlMapList(sb.toString(), params);
		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,ProdProjectForm.class);
		page.setRows(projList);
		return page;
	}

	/* (non-Javadoc)
	 * @see com.hsnn.medstgmini.pro.project.dao.ProdProfileDao#getBidProjectDetailsById(java.lang.String)
	 */
	@Override
	public BidProjectDetails getBidProjectDetailsById(String projId) {
		return (BidProjectDetails) sqlUtil.getUniquerResult(BidProjectDetails.class, projId);
	}
	
}
